package com.cskaoyan.batch;

import com.cskaoyan.utils.JDBCUtils2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/07/26 16:07
 *
 * @author ciggar
 *
 * 批量插入
 */
public class BatchDemo {

    static Connection connection;
    static {
        connection = JDBCUtils2.getConnection();
    }

    public static void main(String[] args) throws SQLException {

        int nums = 1000000;

//        long t1 = System.currentTimeMillis();
//
//        testForInsert(nums);
//
//        long t2 = System.currentTimeMillis();
//
//
//        insertUseStatementBatch(nums);
//
        long t3 = System.currentTimeMillis();


        insertUsePreparedStatement(nums);

        long t4 = System.currentTimeMillis();


//        System.out.println("foreach循环插入的时间是：" + (t2 -t1) + " ms");
//        System.out.println("statement批量插入的时间是：" + (t3 -t2) + " ms");
        System.out.println("PreparedStatement批量插入的时间是：" + (t4 -t3) + " ms");


    }



    // for循环
    public static void testForInsert(int nums) throws SQLException {

        for (int i = 0; i < nums; i++) {

            Statement statement = connection.createStatement();
            String username = "foreach:" + i;
            statement.executeUpdate("insert into user values (null,'"+username+"',null,null)");
            statement.close();
        }

    }

    // statement
    public static void insertUseStatementBatch(int nums) throws SQLException {

        Statement statement = connection.createStatement();

        for (int i = 0; i < nums; i++) {
            String username = "statement:" + i;
            String sql = "insert into user values (null,'"+username+"',null,null)";
            // 添加SQL语句
            statement.addBatch(sql);

        }

        // 执行SQL语句
        statement.executeBatch();
        statement.close();

    }


    // prepareStatement
    public static void insertUsePreparedStatement(int nums) throws SQLException {

        PreparedStatement preparedStatement = connection.prepareStatement("insert into user values (null,?,null,null)");

        for (int i = 0; i < nums; i++) {
            String username = "preparedStatement:" + i;
            // 设置参数
            preparedStatement.setString(1,username);

            // 添加到批处理
            preparedStatement.addBatch();

        }

        // 发送参数，执行SQL语句
        preparedStatement.executeBatch();

        preparedStatement.close();


    }
}
